package jp.co.nes.awssupport.test.utility;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;

import jp.co.nes.awssupport.common.utility.DateUtil;

/**
 * データローダクラスです。<br>
 * JUnitでテストする際のテストデータ作成時に使用します。
 */
public class DataLoader {

    /**
     * デフォルトコンストラクタです。<br>
     * 本クラスはインスタンスを生成しません。
     */
    private DataLoader() {
    }

    /**
     * リザーブド管理を登録します。
     *
     * @param reservedManages リザーブド管理
     */
    public static void insertReservedManage(String[][] reservedManages) {
        String sql = "insert into reserved_manage (ReservedInstanceId, SubscriptionId, PricingPlanId, "
                + "LinkedAccountId, ProductName, RegionCode, MultiAz, ProductDescription, InstanceType, "
                + "UsageStartDate, UsageEndDate,  OfferingType, OfferingClass, Duration, Quantity, ReservationARN, "
                + "YearMonth) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < reservedManages.length; i++) {
                List<Object> objectList = new ArrayList<>();
                objectList.add(reservedManages[i][0]);
                objectList.add(reservedManages[i][1]);
                objectList.add(reservedManages[i][2]);
                objectList.add(reservedManages[i][3]);
                objectList.add(reservedManages[i][4]);
                objectList.add(reservedManages[i][5]);
                objectList.add(reservedManages[i][6]);
                objectList.add(reservedManages[i][7]);
                objectList.add(reservedManages[i][8]);
                objectList.add(DateUtil.toTimestamp(DateUtil.toZonedDateTime(reservedManages[i][9])));
                objectList.add(DateUtil.toTimestamp(DateUtil.toZonedDateTime(reservedManages[i][10])));
                objectList.add(reservedManages[i][11]);
                objectList.add(reservedManages[i][12]);
                objectList.add(Integer.parseInt(reservedManages[i][13]));
                objectList.add(new BigDecimal(reservedManages[i][14]));
                objectList.add(reservedManages[i][15]);
                objectList.add(Integer.parseInt(reservedManages[i][16]));
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 融通リザーブド管理を登録します。
     *
     * @param blendedReservedManages 融通リザーブド管理
     */
    public static void insertBlendedReservedManage(String[][] blendedReservedManages) {
        String sql = "insert into blended_reserved_manage (UUID, BucketName, ObjectKey, RecordId, PricingPlanId, "
                + "LinkedAccountId, UsageStartDate, UsageEndDate, UsageQuantity) values(?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < blendedReservedManages.length; i++) {
                List<Object> objectList = new ArrayList<>();
                objectList.add(blendedReservedManages[i][0]);
                objectList.add(blendedReservedManages[i][1]);
                objectList.add(blendedReservedManages[i][2]);
                objectList.add(blendedReservedManages[i][3]);
                objectList.add(blendedReservedManages[i][4]);
                objectList.add(blendedReservedManages[i][5]);
                objectList.add(DateUtil.toTimestamp(DateUtil.toZonedDateTime(blendedReservedManages[i][6])));
                objectList.add(DateUtil.toTimestamp(DateUtil.toZonedDateTime(blendedReservedManages[i][7])));
                objectList.add(new BigDecimal(blendedReservedManages[i][8]));
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 日次集計結果を登録します。
     *
     * @param dailyCompiledBillings 日次集計結果
     */
    public static void insertDailyCompiledBilling(String[][] dailyCompiledBillings) {
        String sql = "insert into daily_compiled_billing (UUID, LinkedAccountId, YearMonth, ProductName, UsageType, "
                + "UsageStartDate, UsageEndDate, ResourceId, NameTag, SpotManageFlg, Rate, UsageQuantity, "
                + "Description, Unit) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < dailyCompiledBillings.length; i++) {
                List<Object> objectList = new ArrayList<>();
                objectList.add(dailyCompiledBillings[i][0]);
                objectList.add(dailyCompiledBillings[i][1]);
                objectList.add(Integer.parseInt(dailyCompiledBillings[i][2]));
                objectList.add(dailyCompiledBillings[i][3]);
                objectList.add(dailyCompiledBillings[i][4]);
                objectList.add(DateUtil.toTimestamp(DateUtil.toZonedDateTime(dailyCompiledBillings[i][5])));
                objectList.add(DateUtil.toTimestamp(DateUtil.toZonedDateTime(dailyCompiledBillings[i][6])));
                objectList.add(dailyCompiledBillings[i][7]);
                objectList.add(dailyCompiledBillings[i][8]);
                objectList.add(dailyCompiledBillings[i][9]);
                objectList.add(new BigDecimal(dailyCompiledBillings[i][10]));
                objectList.add(new BigDecimal(dailyCompiledBillings[i][11]));
                objectList.add(dailyCompiledBillings[i][12]);
                objectList.add(dailyCompiledBillings[i][13]);
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 月次集計結果を登録します。
     *
     * @param monthlyCompiledBillings 月次集計結果の配列
     */
    public static void insertMonthlyCompiledBilling(String[][] monthlyCompiledBillings) {
        String sql = "insert into monthly_compiled_billing (LinkedAccountId, YearMonth, ProductName, "
                + "UsageType, Rate, UsageQuantity, BillingAmount, Description, Unit) "
                + "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < monthlyCompiledBillings.length; i++) {
//System.out.println("i=" + i + "(" + monthlyCompiledBillings[i][0] + ")");
                List<Object> objectList = new ArrayList<>();
                objectList.add(monthlyCompiledBillings[i][0]);
                objectList.add(Integer.parseInt(monthlyCompiledBillings[i][1]));
                objectList.add(monthlyCompiledBillings[i][2]);
                objectList.add(monthlyCompiledBillings[i][3]);
                objectList.add(new BigDecimal(monthlyCompiledBillings[i][4]));
                objectList.add(new BigDecimal(monthlyCompiledBillings[i][5]));
                objectList.add(new BigDecimal(monthlyCompiledBillings[i][6]));
                objectList.add(monthlyCompiledBillings[i][7]);
                objectList.add(monthlyCompiledBillings[i][8]);
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * キャンペーンプランを登録します。
     *
     * @param plans キャンペーンプランの配列
     */
    public static void insertCampaignPlan(String[][] plans) {
        String sql = "insert into campaign_plan (PlanId, CampaignGroupId, ProductName, UsageType, Coefficient, "
                + "Constant, StartDate, EndDate, Description) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < plans.length; i++) {
                List<Object> objectList = new ArrayList<>();
                objectList.add(plans[i][0]);
                objectList.add(plans[i][1]);
                objectList.add(plans[i][2]);
                objectList.add(plans[i][3]);
                objectList.add(new BigDecimal(plans[i][4]));
                objectList.add(new BigDecimal(plans[i][5]));
                objectList.add(DateUtil.toTimestamp(
                        DateUtil.toZonedDateTime(Integer.parseInt(plans[i][6]), Integer.parseInt(plans[i][7]), 1)));
                objectList.add(DateUtil.toTimestamp(
                        DateUtil.toZonedDateTime(Integer.parseInt(plans[i][8]), Integer.parseInt(plans[i][9]), 1)));
                objectList.add(null);
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * キャンペーン適用を登録します。
     *
     * @param assigned キャンペーン適用の配列
     */
    public static void insertCampaignAssigned(String[][] assigned) {
        String sql = "insert into campaign_assigned (LinkedAccountId, YearMonth, AssignedPlanId, "
                + "AssignedGroupId, AssignedCampaignName, TargetAmount, CampaignAmount) values (?, ?, ?, ?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < assigned.length; i++) {
                List<Object> objectList = new ArrayList<>();
                objectList.add(assigned[i][0]);
                objectList.add(Integer.parseInt(assigned[i][1]));
                objectList.add(assigned[i][2]);
                objectList.add(assigned[i][3]);
                objectList.add(assigned[i][4]);
                objectList.add(Long.parseLong(assigned[i][5]));
                objectList.add(Long.parseLong(assigned[i][6]));
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * クレジット適用を登録します。
     *
     * @param assigned クレジット適用の配列
     */
    public static void insertCreditCouponAssigned(String[][] assigned) {
        String sql = "insert into credit_coupon_assigned (LinkedAccountId, YearMonth, CreditCouponId, "
                + "CreditCouponHistoryKind, TargetAmount, CreditCouponAmount, CreditCouponAmountUSDollar) "
                + "values (?, ?, ?, ?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < assigned.length; i++) {
                List<Object> objectList = new ArrayList<>();
                objectList.add(assigned[i][0]);
                objectList.add(Integer.parseInt(assigned[i][1]));
                objectList.add(assigned[i][2]);
                objectList.add(assigned[i][3]);
                objectList.add(Long.parseLong(assigned[i][4]));
                objectList.add(Long.parseLong(assigned[i][5]));
                objectList.add(new BigDecimal(assigned[i][6]));
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 雑費適用を登録します。
     *
     * @param assigned 雑費適用の配列
     */
    public static void insertMiscellaneousExpensesAssigned(String[][] assigned) {
        String sql = "insert into miscellaneous_expenses_assigned (LinkedAccountId, YearMonth, AssignedPlanId, "
                + "AssignedGroupId, AssignedMiscellaneousExpensesName, TargetAmount, MiscellaneousExpensesAmount) "
                + "values (?, ?, ?, ?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < assigned.length; i++) {
                List<Object> objectList = new ArrayList<>();
                objectList.add(assigned[i][0]);
                objectList.add(Integer.parseInt(assigned[i][1]));
                objectList.add(assigned[i][2]);
                objectList.add(assigned[i][3]);
                objectList.add(assigned[i][4]);
                objectList.add(Long.parseLong(assigned[i][5]));
                objectList.add(Long.parseLong(assigned[i][6]));
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * プレミアムサポート適用を登録します。
     *
     * @param assigned プレミアムサポート適用の配列
     */
    public static void insertPremiumSupportAssigned(String[][] assigned) {
        String sql = "insert into premiumsupport_assigned (LinkedAccountId, YearMonth, TargetAmount, "
                + "PremiumSupportAmount) values (?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < assigned.length; i++) {
                List<Object> objectList = new ArrayList<>();
                objectList.add(assigned[i][0]);
                objectList.add(Integer.parseInt(assigned[i][1]));
                objectList.add(Long.parseLong(assigned[i][2]));
                objectList.add(Long.parseLong(assigned[i][3]));
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 税金適用を登録します。
     *
     * @param assigned 税金適用の配列
     */
    public static void insertTaxAssigned(String[][] assigned) {
        String sql = "insert into tax_assigned (LinkedAccountId, YearMonth, AssignedPlanId, AssignedGroupId, "
                + "AssignedTaxName, TargetAmount, TaxAmount) values (?, ?, ?, ?, ?, ?, ?)";
        try {
            Connection connection = JUnitDbConnUtil.getConn();

            List<Object[]> paramList = new ArrayList<>();
            for (int i = 0; i < assigned.length; i++) {
                List<Object> objectList = new ArrayList<>();
                objectList.add(assigned[i][0]);
                objectList.add(Integer.parseInt(assigned[i][1]));
                objectList.add(assigned[i][2]);
                objectList.add(assigned[i][3]);
                objectList.add(assigned[i][4]);
                objectList.add(new BigDecimal(assigned[i][5]));
                objectList.add(new BigDecimal(assigned[i][6]));
                paramList.add(objectList.toArray());
            }

            QueryRunner runner = new QueryRunner();
            runner.batch(connection, sql, paramList.toArray(new Object[0][0]));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
